using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frmbc_bangluongchitiet : Form
    {
        AccessData mDB = new AccessData();
        Language lan = new Language();
        DataSet ds = new DataSet();
        DataSet ds_nv = new DataSet();
        string sql = "", thang = "", nam = "", s_mmyy = "",user="",user_dn="";
        string s_vk = "", s_tlvk = "", s_stvk = "", s_cv = "", s_tlcv = "", s_stcv = "", s_db = "", s_tldb = "", s_stdb = "", s_th = "", s_tlth = "", s_stth = "",
            s_ld = "", s_tlld = "", s_stld = "", s_dh = "", s_tldh = "", s_stdh = "", s_kv = "", s_tlkv = "", s_stkv = "",
            s_dt = "", s_tldt = "", s_stdt = "", s_ud = "", s_tlud = "", s_stud = "", s_tn = "", s_tltn = "", s_sttn = "",
            s_ta = "", s_tlta = "", s_stta = "", s_tx = "", s_tltx = "", s_sttx = "", s_tdt = "", s_tltdt = "", s_sttdt = "", s_kh = "", s_tlkh = "", s_stkh = "";
        decimal s_tltv=0,s_tldihoc=0,s_tllths=0;
        decimal ngaylv_thucte = 0, ngaylv_quydinh = 0;
        int n_sudungdlcc=0;
        public frmbc_bangluongchitiet(string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            user_dn = s_userdn;
        }

        private void frmbc_bangluongchitiet_Load(object sender, EventArgs e)
        {
            num_thang.Value = DateTime.Now.Month;
            num_nam.Value = DateTime.Now.Year;
            user = mDB.user;
            thang=num_thang.Value.ToString().Trim().PadLeft(2, '0');
            nam=num_nam.Value.ToString().Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            load_bophan();
            load_loaihd();
            this.WindowState = FormWindowState.Maximized;
        }
        private void load_tsphucap(string s_mmyy)
        {
            //load thong so he so phu cap
            s_vk = mDB.h_hsvk(s_mmyy).Trim();
            s_stvk = mDB.h_stvk(s_mmyy).Trim();
            s_tlvk = mDB.h_tlvk(s_mmyy).Trim();
            s_cv = mDB.h_hscv(s_mmyy).Trim();
            s_stcv = mDB.h_stcv(s_mmyy).Trim();
            s_tlcv = mDB.h_tlcv(s_mmyy).Trim();
            s_db = mDB.h_hsdb(s_mmyy).Trim();
            s_stdb = mDB.h_stdb(s_mmyy).Trim();
            s_tldb = mDB.h_tldb(s_mmyy).Trim();
            s_dt = mDB.h_hsdt(s_mmyy).Trim();
            s_stdt = mDB.h_stdt(s_mmyy).Trim();
            s_tldt = mDB.h_tldt(s_mmyy).Trim();
            s_th = mDB.h_hsth(s_mmyy).Trim();
            s_stth = mDB.h_stth(s_mmyy).Trim();
            s_tlth = mDB.h_tlth(s_mmyy).Trim();
            s_ld = mDB.h_hsld(s_mmyy).Trim();
            s_stld = mDB.h_stld(s_mmyy).Trim();
            s_tlld = mDB.h_tlld(s_mmyy).Trim();
            s_dh = mDB.h_hsdh(s_mmyy).Trim();
            s_stdh = mDB.h_stdh(s_mmyy).Trim();
            s_tldh = mDB.h_tldh(s_mmyy).Trim();
            s_kv = mDB.h_hskv(s_mmyy).Trim();
            s_stkv = mDB.h_stkv(s_mmyy).Trim();
            s_tlkv = mDB.h_tlkv(s_mmyy).Trim();
            s_dt = mDB.h_hsdt(s_mmyy).Trim();
            s_stdt = mDB.h_stdt(s_mmyy).Trim();
            s_tldt = mDB.h_tldt(s_mmyy).Trim();
            s_ud = mDB.h_hsud(s_mmyy).Trim();
            s_stud = mDB.h_stud(s_mmyy).Trim();
            s_tlud = mDB.h_tlud(s_mmyy).Trim();
            s_tn = mDB.h_hstn(s_mmyy).Trim();
            s_sttn = mDB.h_sttn(s_mmyy).Trim();
            s_tltn = mDB.h_tltn(s_mmyy).Trim();
            s_ta = mDB.h_hsta(s_mmyy).Trim();
            s_stta = mDB.h_stta(s_mmyy).Trim();
            s_tlta = mDB.h_tlta(s_mmyy).Trim();
            s_tx = mDB.h_hstx(s_mmyy).Trim();
            s_sttx = mDB.h_sttx(s_mmyy).Trim();
            s_tltx = mDB.h_tltx(s_mmyy).Trim();
            s_tdt = mDB.h_hstdt(s_mmyy).Trim();
            s_sttdt = mDB.h_sttdt(s_mmyy).Trim();
            s_tltdt = mDB.h_tltdt(s_mmyy).Trim();
            s_kh = mDB.h_hskh(s_mmyy).Trim();
            s_stkh = mDB.h_stkh(s_mmyy).Trim();
            s_tlkh = mDB.h_tlkh(s_mmyy).Trim();
            //
            s_tltv = mDB.tyle_tv(s_mmyy).Trim() != "" ? decimal.Parse(mDB.tyle_tv(s_mmyy).Trim()) : 0;
            s_tldihoc = mDB.tyle_dh(s_mmyy).Trim() != "" ? decimal.Parse(mDB.tyle_dh(s_mmyy).Trim()) : 0;
            s_tllths = mDB.tyle_lths(s_mmyy).Trim() != "" ? decimal.Parse(mDB.tyle_lths(s_mmyy).Trim()) : 0;
            ngaylv_thucte = mDB.ngaylv_thucte(s_mmyy);
            ngaylv_quydinh = mDB.ngaylv_quydinh(s_mmyy);
        }
        private void load_bophan()
        {
            cbo_bophan.DisplayMember = "tenbp";
            cbo_bophan.ValueMember = "madv";
            cbo_bophan.DataSource = mDB.get_bophan().Tables[0];
            cbo_bophan.SelectedIndex = -1;
        }
        private void load_loaihd()
        {
            cbo_loaihd.DisplayMember = "tennhom";
            cbo_loaihd.ValueMember = "manhom";
            cbo_loaihd.DataSource = mDB.get_nhombc().Tables[0];
            cbo_loaihd.SelectedIndex = -1;
        }
        private DataSet get_data()
        {
            if (chk_sudungdlcc.Checked==true)
            {
                n_sudungdlcc = 1;
            }
            else
            {
                n_sudungdlcc = 0;
            }
            sql = " select l.manv,trim(l.ho)||' '||trim(l.ten) as hoten,cd.ten as chucdanh,cv.diengiai as chucvu";
            sql += " ,case when l.nnghiep <>'' then trim(ct.tenctdt)||' - '||trim(bc.tenbang) else  trim(bc.tenbang) end as td_chuyenmon";
            sql += " ,hnv.bhxh,hnv.bhyt,hnv.bhtn,hnv.h_hsbdhv,hnv.h_hslc,hnv.h_hscv,hnv.h_hsvk,hnv.h_hstn,hnv.h_hskv,hnv.h_hsdh,hnv.h_hsdb,hnv.h_hsdt,hnv.h_hsld,hnv.h_hsth";
            sql += " ,hnv.h_pcta,hnv.h_pctx,hnv.h_pctdt,hnv.h_pckh,hnv.h_sotienhd,hnv.h_tonghs,hnv.h_hsud,hnv.thuviec,hnv.dihoc,hnv.luutruhs,hnv.kpcd,hnv.loaihd,hnv.h_stbantg";
            sql += " ,tcc.ts_coluong,tcc.ts_kluong,tcc.ts_bhtra,tcc.ts_khac,tcc.ts_ngaycong,tcc.ts_ngaytru";
            sql += " ,hs.mltt,hs.mbhxh_hs,hs.mbhyt_hs,hs.mbhtn_hs,hs.hs2,hs.hs3,hs.hs4,hs.mom_hs,hs.lg_tapsu,hs.lg_dihoc,hs.mkpcd_hs";

            sql += " ,0.0 as st_luongcb,0.0 as st_cv,0.0 as st_vk,0.0 as st_dh,0.0 as st_ld,0.0 as st_ud,0.0 as st_th,0.0 as st_kv,0.0 as st_tn,0.0 as st_tx,0.0 as st_ta,0.0 as st_dt,0.0 as st_db";
            sql += " ,0.0 as st_tdt,0.0 as st_khac,0.0 as st_hopdong,0.0 as st_tongluong,0.0 as st_bhxh,0.0 as st_bhyt,0.0 as st_bhtn,0.0 as st_kpcd,0.0 as st_bantg";
            sql += " from nhansu.llnv l inner join nhansu.hopdong hd on l.manv=hd.manv";
            sql += " inner join nhansu.th_chamcong_0811 tcc on l.manv=tcc.manv";
            sql += " inner join nhansu.heso_nv_0811 hnv on l.manv=hnv.manv";
            sql += " inner join nhansu.heso hs on hs.mmyy='0811'";
            sql += " inner join nhansu.bophan bp on l.madv=bp.madv";
            sql += " left join nhansu.ctdaotao ct on l.nnghiep=ct.mactdt";
            sql += " left join nhansu.bangcap bc on l.td_nnghiep =bc.mabangcap";
            sql += " left join nhansu.dmcd cd on l.macd=cd.ma";
            sql += " left join nhansu.dmcv cv on hd.macv=cv.macv";
            sql += " left join nhansu.mahd mh on hd.loaihd=mh.mahd";
            sql += " inner join nhansu.max_lan ml on hd.manv=ml.manv";
            sql += " where hd.lan=ml.lan";
            if (cbo_bophan.SelectedIndex != -1)
                sql += " and l.madv='"+cbo_bophan.SelectedValue.ToString().Trim()+"'";
            if (cbo_loaihd.SelectedIndex != -1)
            {
                sql += " and mh.nhombc = "+cbo_loaihd.SelectedValue.ToString().Trim()+"";
            }
            sql += " order by bp.sothutu,cv.stt,cd.thutu";
            ds_nv= mDB.get_data(sql);
            if (ds_nv.Tables[0].Rows.Count > 0)
            {
                string manv = "",s_lhdbantg="";
                int bhxh = 0, bhyt = 0, bhtn = 0, kpcd = 0, thuviec = 0, luutruhs = 0, dihoc = 0,h_luongsp=0;
                decimal mltt=0,h_hslc = 0, h_hsvk = 0, h_hscv = 0, h_hstn = 0, h_hsdh = 0, h_hskv = 0, h_hsld = 0, h_hsth = 0, h_hsdt = 0, h_hsud = 0, h_hsdb = 0, h_pcta = 0, h_pctx = 0, h_pctdt = 0, h_pckh = 0;
                decimal st_luongcb = 0, st_cv = 0, st_vk = 0, st_dh = 0, st_ld = 0, st_ud = 0, st_th = 0, st_kv = 0, st_tn = 0, st_tx = 0, st_ta = 0, st_dt = 0, st_db = 0
                    ,st_tdt = 0,st_khac = 0,st_hopdong=0, st_tongluong = 0,st_bhxh=0,st_bhyt=0,st_bhtn=0,st_kpcd=0,st_bantg=0;
                decimal ts_coluong = 0, ts_kluong = 0, ts_bhtra = 0, ts_khac = 0, ts_ngaycong = 0, ts_ngaytru = 0;

                foreach (DataRow dr in ds_nv.Tables[0].Rows)
                {
                    manv = dr["manv"].ToString().Trim();
                    bhxh = dr["bhxh"].ToString().Trim()!="" ? Int32.Parse(dr["bhxh"].ToString().Trim()):0;
                    bhyt = dr["bhyt"].ToString().Trim()!="" ? Int32.Parse(dr["bhyt"].ToString().Trim()):0;
                    bhtn = dr["bhtn"].ToString().Trim()!="" ? Int32.Parse(dr["bhtn"].ToString().Trim()):0;
                    kpcd = dr["kpcd"].ToString().Trim()!="" ? Int32.Parse(dr["kpcd"].ToString().Trim()):0;
                    thuviec = dr["thuviec"].ToString().Trim()!="" ? Int32.Parse(dr["thuviec"].ToString().Trim()):0;
                    luutruhs = dr["luutruhs"].ToString().Trim() != "" ? Int32.Parse(dr["luutruhs"].ToString().Trim()) : 0;
                    dihoc = dr["dihoc"].ToString().Trim() != "" ? Int32.Parse(dr["dihoc"].ToString().Trim()) : 0;
                    ts_coluong = dr["ts_coluong"].ToString().Trim() != "" ? decimal.Parse(dr["ts_coluong"].ToString().Trim()) : 0; 
                    ts_kluong = dr["ts_kluong"].ToString().Trim() != "" ? decimal.Parse(dr["ts_kluong"].ToString().Trim()) : 0; 
                    ts_bhtra = dr["ts_bhtra"].ToString().Trim() != "" ? decimal.Parse(dr["ts_bhtra"].ToString().Trim()) : 0; 
                    ts_khac = dr["ts_khac"].ToString().Trim() != "" ? decimal.Parse(dr["ts_khac"].ToString().Trim()) : 0;
                    ts_ngaycong = dr["ts_ngaycong"].ToString().Trim() != "" ? decimal.Parse(dr["ts_ngaycong"].ToString().Trim()) : 0;
                    ts_ngaytru = dr["ts_ngaytru"].ToString().Trim() != "" ? decimal.Parse(dr["ts_ngaytru"].ToString().Trim()) : 0;
                    //

                    mltt = dr["mltt"].ToString().Trim() != "" ? decimal.Parse(dr["mltt"].ToString().Trim()) : 0;
                    h_hslc = dr["h_hslc"].ToString().Trim() != "" ? decimal.Parse(dr["h_hslc"].ToString().Trim()) : 0;
                    h_hsvk = dr["h_hsvk"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsvk"].ToString().Trim()) : 0;
                    h_hscv = dr["h_hscv"].ToString().Trim() != "" ? decimal.Parse(dr["h_hscv"].ToString().Trim()) : 0;
                    h_hstn = dr["h_hstn"].ToString().Trim() != "" ? decimal.Parse(dr["h_hstn"].ToString().Trim()) : 0;
                    h_hsdh = dr["h_hsdh"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsdh"].ToString().Trim()) : 0;
                    h_hskv = dr["h_hskv"].ToString().Trim() != "" ? decimal.Parse(dr["h_hskv"].ToString().Trim()) : 0;
                    h_hsld = dr["h_hsld"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsld"].ToString().Trim()) : 0;
                    h_hsth = dr["h_hsth"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsth"].ToString().Trim()) : 0;
                    h_hsdt = dr["h_hsdt"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsdt"].ToString().Trim()) : 0;
                    h_hsud = dr["h_hsud"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsud"].ToString().Trim()) : 0;
                    h_hsdb = dr["h_hsdb"].ToString().Trim() != "" ? decimal.Parse(dr["h_hsdb"].ToString().Trim()) : 0;
                    h_pcta = dr["h_pcta"].ToString().Trim() != "" ? decimal.Parse(dr["h_pcta"].ToString().Trim()) : 0;
                    h_pctx = dr["h_pctx"].ToString().Trim() != "" ? decimal.Parse(dr["h_pctx"].ToString().Trim()) : 0;
                    h_pctdt = dr["h_pctdt"].ToString().Trim() != "" ? decimal.Parse(dr["h_pctdt"].ToString().Trim()) : 0;
                    h_pckh = dr["h_pckh"].ToString().Trim() != "" ? decimal.Parse(dr["h_pckh"].ToString().Trim()) : 0;
                    st_hopdong = dr["h_sotienhd"].ToString().Trim() != "" ? decimal.Parse(dr["h_sotienhd"].ToString().Trim()) : 0;
                    st_bantg = dr["h_stbantg"].ToString().Trim() != "" ? decimal.Parse(dr["h_stbantg"].ToString().Trim()) : 0;
                    st_luongcb = h_hslc * mltt;
                    if (thuviec == 1)
                    {
                        st_luongcb = h_hslc * mltt*s_tltv/100;
                    }
                    if (dihoc == 1)
                    {
                        st_luongcb = h_hslc * mltt * s_tldihoc / 100;
                    }
                    if (luutruhs == 1)
                    {
                        st_luongcb = h_hslc * mltt * s_tllths / 100;
                    }
                    
                    if (s_vk.Trim() == "1")
                    {
                        if (s_stvk.Trim() == "1")
                        {
                            st_vk = h_hsvk;
                        }
                        if (s_tlvk.Trim() == "1")
                        {
                            st_vk = h_hslc * h_hsvk * mltt;
                        }
                        else
                        {
                            st_vk = h_hsvk * mltt;
                        }
                    }
                    //
                    if (s_cv.Trim() == "1")
                    {
                        if (s_stcv.Trim() == "1")
                        {
                            st_cv = h_hscv;
                        }
                        else
                        {
                            st_cv = h_hscv * mltt;
                        }
                    }
                    //
                    if (s_dh.Trim() == "1")
                    {
                        if (s_stdh.Trim() == "1")
                        {
                            st_dh = h_hsdh;
                        }
                        else
                        {
                            st_dh = h_hsdh * mltt;
                        }
                    }
                    //
                    if (s_ld.Trim() == "1")
                    {
                        if (s_stld.Trim() == "1")
                        {
                            st_ld = h_hsld;
                        }
                        else
                        {
                            st_ld = h_hsld * mltt;
                        }
                    }
                    //
                    if (s_ud.Trim() == "1")
                    {
                        if (s_stud.Trim() == "1")
                        {
                            st_ud = h_hsud;
                        }
                        if (s_tlud.Trim() == "1")
                        {
                            st_ud = (h_hsud * (h_hslc * (1 + h_hsvk))) * mltt / 100;
                        }
                        else
                        {
                            st_ud = h_hsud * mltt;
                        }
                    }
                    //
                    if (s_th.Trim() == "1")
                    {
                        if (s_stth.Trim() == "1")
                        {
                            st_th = h_hsth;
                        }
                        else
                        {
                            st_th = h_hsth * mltt;
                        }
                    }
                    //
                    if (s_kv.Trim() == "1")
                    {
                        if (s_stkv.Trim() == "1")
                        {
                            st_kv = h_hskv;
                        }
                        else
                        {
                            st_kv = h_hskv * mltt;
                        }
                    }
                    //
                    if (s_tn.Trim() == "1")
                    {
                        if (s_sttn.Trim() == "1")
                        {
                            st_tn = h_hstn;
                        }
                        else
                        {
                            st_tn = h_hstn * mltt;
                        }
                    }
                    //
                    if (s_db.Trim() == "1")
                    {
                        if (s_stdb.Trim() == "1")
                        {
                            st_db = h_hsdb;
                        }
                        if (s_tldb.Trim() == "1")
                        {
                            st_db = (h_hsdb * h_hslc) * mltt / 100;
                        }
                        else
                        {
                            st_db = h_hsdb * mltt;
                        }
                    }
                    //
                    if (s_dt.Trim() == "1")
                    {
                        if (s_stdt.Trim() == "1")
                        {
                            st_dt = h_hsdt;
                        }
                        if (s_tldt.Trim() == "1")
                        {
                            st_dt = (h_hsdt * h_hslc) * mltt / 100;
                        }
                        else
                        {
                            st_dt = h_hsdt * mltt;
                        }
                    }
                    //
                    if (s_ta.Trim() == "1")
                    {
                        if (s_stta.Trim() == "1")
                        {
                            st_ta = h_pcta;
                        }
                        else
                        {
                            st_ta = h_pcta * mltt;
                        }
                    }
                    //
                    if (s_tx.Trim() == "1")
                    {
                        if (s_sttx.Trim() == "1")
                        {
                            st_tx = h_pctx;
                        }
                        else
                        {
                            st_tx = h_pctx * mltt;
                        }
                    }
                    //
                    if (s_tdt.Trim() == "1")
                    {
                        if (s_sttdt.Trim() == "1")
                        {
                            st_tdt = h_pctdt;
                        }
                        else
                        {
                            st_tdt = h_pctdt * mltt;
                        }
                    }
                    //
                    if (s_kh.Trim() == "1")
                    {
                        if (s_stkh.Trim() == "1")
                        {
                            st_khac = h_pckh;
                        }
                        else
                        {
                            st_khac = h_pckh * mltt;
                        }
                    }
                    //tinh tien dong bao hiem
                    if(bhxh == 1)
                    {
                        st_bhxh = get_tbhxh(manv, s_mmyy);
                    }
                    if(bhyt == 1)
                    {
                        st_bhyt = get_tbhyt(manv, s_mmyy);
                    }
                    if(bhtn == 1)
                    {
                        st_bhtn = get_tbhtn(manv, s_mmyy);
                    }
                    if(bhxh == 1)
                    {
                        st_kpcd = get_tkpcd(manv, s_mmyy);
                    }
                    //
                    if (n_sudungdlcc == 1)
                    {

                    }

                    //gan du lieu vao lai dataset
                    dr["st_luongcb"]=st_luongcb.ToString();
                    dr["st_cv"]=st_cv.ToString();
                    dr["st_vk"]=st_vk.ToString();
                    dr["st_dh"]=st_dh.ToString();
                    dr["st_ld"]=st_ld.ToString();
                    dr["st_ud"]=st_ud.ToString();
                    dr["st_th"]=st_th.ToString();
                    dr["st_kv"]=st_kv.ToString();
                    dr["st_tn"]=st_tn.ToString();
                    dr["st_tx"]=st_tx.ToString();
                    dr["st_ta"]=st_ta.ToString();
                    dr["st_dt"]=st_dt.ToString();
                    dr["st_db"]=st_db.ToString();
                    dr["st_tdt"]=st_tdt.ToString();
                    dr["st_khac"]=st_khac.ToString();
                    dr["st_hopdong"]=st_hopdong.ToString();
                    dr["st_tongluong"]=st_tongluong.ToString();
                    dr["st_bhxh"]=st_bhxh.ToString();
                    dr["st_bhyt"]=st_bhyt.ToString();
                    dr["st_bhtn"]=st_bhtn.ToString();
                    dr["st_kpcd"]=st_kpcd.ToString();
                    dr["st_bantg"]=st_bantg.ToString();

                }
                
            }
            return ds_nv;
        }
        private decimal get_tbhxh(string manv, string mmyy)
        {
            decimal t_bhxh=0;
            sql = " select l.manv,round(case hnv.bhxh=1 when hb.luongkhoan='true' then hnv.h_sotienhd*mbhxh_hs";
            sql += " else (case when hb.hs_lc='true' then hnv.h_hslc else 0 end + ";
            sql += " case when hb.hs_vk='true' then hnv.h_hslc*hnv.h_hsvk else 0 end +"; 
            sql += " case when hb.hs_cv='true' then hnv.h_hscv else 0 end +";
            sql += " case when hb.hs_kv='true' then hnv.h_hskv else 0 end +";
            sql += " case when hb.hs_tn='true' then hnv.h_hstn else 0 end +";
            sql += " case when hb.hs_dh='true' then hnv.h_hsdh else 0 end)*mbhxh_hs*mltt end,2) as t_bhxh";
            sql += " from "+user+".heso_nv_"+mmyy+" hnv inner join "+user+".llnv l on hnv.manv=l.manv";
            sql += " left join "+user+".heso_bh hb on hb.mmyy='"+mmyy+"'";
            sql += " left join "+user+".heso hs on hs.mmyy='"+mmyy+"'";
            sql += " where hb.loaibh='bhxh' and hb.loaihd=hnv.loaihd and hnv.manv='"+manv+"'";
            ds = mDB.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                t_bhxh = decimal.Parse(ds.Tables[0].Rows[0]["t_bhxh"].ToString());
            }
            return t_bhxh;
        }
        private decimal get_tbhyt(string manv, string mmyy)
        {
            decimal t_bhyt = 0;
            sql = " select l.manv,round(case hnv.bhyt=1 when hb.luongkhoan='true' then hnv.h_sotienhd*mbhyt_hs";
            sql += " else (case when hb.hs_lc='true' then hnv.h_hslc else 0 end + ";
            sql += " case when hb.hs_vk='true' then hnv.h_hslc*hnv.h_hsvk else 0 end +";
            sql += " case when hb.hs_cv='true' then hnv.h_hscv else 0 end +";
            sql += " case when hb.hs_kv='true' then hnv.h_hskv else 0 end +";
            sql += " case when hb.hs_tn='true' then hnv.h_hstn else 0 end +";
            sql += " case when hb.hs_dh='true' then hnv.h_hsdh else 0 end)*mbhyt_hs*mltt end,2) as t_bhyt";
            sql += " from "+user+".heso_nv_" + mmyy + " hnv inner join "+user+".llnv l on hnv.manv=l.manv";
            sql += " left join "+user+".heso_bh hb on hb.mmyy='" + mmyy + "'";
            sql += " left join "+user+".heso hs on hs.mmyy='" + mmyy + "'";
            sql += " where hb.loaibh='bhyt' and hb.loaihd=hnv.loaihd and hnv.manv='" + manv + "'";
            ds = mDB.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                t_bhyt = decimal.Parse(ds.Tables[0].Rows[0]["t_bhyt"].ToString());
            }
            return t_bhyt;
        }
        private decimal get_tbhtn(string manv, string mmyy)
        {
            decimal t_bhtn = 0;
            sql = " select l.manv,round(case hnv.bhtn=1 when hb.luongkhoan='true' then hnv.h_sotienhd*mbhtn_hs";
            sql += " else (case when hb.hs_lc='true' then hnv.h_hslc else 0 end + ";
            sql += " case when hb.hs_vk='true' then hnv.h_hslc*hnv.h_hsvk else 0 end +";
            sql += " case when hb.hs_cv='true' then hnv.h_hscv else 0 end +";
            sql += " case when hb.hs_kv='true' then hnv.h_hskv else 0 end +";
            sql += " case when hb.hs_tn='true' then hnv.h_hstn else 0 end +";
            sql += " case when hb.hs_dh='true' then hnv.h_hsdh else 0 end)*mbhtn_hs*mltt end,2) as t_bhtn";
            sql += " from " + user + ".heso_nv_" + mmyy + " hnv inner join " + user + ".llnv l on hnv.manv=l.manv";
            sql += " left join " + user + ".heso_bh hb on hb.mmyy='" + mmyy + "'";
            sql += " left join " + user + ".heso hs on hs.mmyy='" + mmyy + "'";
            sql += " where hb.loaibh='bhtn' and hb.loaihd=hnv.loaihd and hnv.manv='" + manv + "'";
            ds = mDB.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                t_bhtn = decimal.Parse(ds.Tables[0].Rows[0]["t_bhtn"].ToString());
            }
            return t_bhtn;
        }
        private decimal get_tkpcd(string manv, string mmyy)
        {
            decimal t_kpcd = 0;
            sql = " select l.manv,round(case hnv.kpcd=1 when hb.luongkhoan='true' then hnv.h_sotienhd*mkpcd_hs";
            sql += " else (case when hb.hs_lc='true' then hnv.h_hslc else 0 end + ";
            sql += " case when hb.hs_vk='true' then hnv.h_hslc*hnv.h_hsvk else 0 end +";
            sql += " case when hb.hs_cv='true' then hnv.h_hscv else 0 end +";
            sql += " case when hb.hs_kv='true' then hnv.h_hskv else 0 end +";
            sql += " case when hb.hs_tn='true' then hnv.h_hstn else 0 end +";
            sql += " case when hb.hs_dh='true' then hnv.h_hsdh else 0 end)*mkpcd_hs*mltt end,2) as t_kpcd";
            sql += " from " + user + ".heso_nv_" + mmyy + " hnv inner join " + user + ".llnv l on hnv.manv=l.manv";
            sql += " left join " + user + ".heso_bh hb on hb.mmyy='" + mmyy + "'";
            sql += " left join " + user + ".heso hs on hs.mmyy='" + mmyy + "'";
            sql += " where hb.loaibh='kpcd' and hb.loaihd=hnv.loaihd and hnv.manv='" + manv + "'";
            ds = mDB.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                t_kpcd = decimal.Parse(ds.Tables[0].Rows[0]["t_kpcd"].ToString());
            }
            return t_kpcd;
        }
        
        private void btn_xem_Click(object sender, EventArgs e)
        {
            thang = num_thang.Value.ToString().Trim().PadLeft(2, '0');
            nam = num_nam.Value.ToString().Trim();
            s_mmyy = thang + nam.Substring(2, 2);
            load_tsphucap(s_mmyy);
            get_data();
        }

        private void btn_sua_Click(object sender, EventArgs e)
        {

        }

        private void btn_luu_Click(object sender, EventArgs e)
        {

        }

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}